package com.dao;

import com.bean.Subsidy;
import com.util.DBSCUtils;
import com.util.SQLUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


public class SubsidyManagementDaoImp implements ISubsidyManagementDao
{
    ResultSet resultSet = null;

    /**
     *
     * @param subsidyProjectName 需要查询的项目名称
     * @return 查询到名称一致的项目
     */
    @Override
    public Subsidy[] SearchSubsidyByName(String subsidyProjectName)
    {
        //初始化sql语句结果集和返回列表
        String sqlStr = "select * from subsidy where subsidy_project_name like ?";
        List<Subsidy> subsidyList= new ArrayList<>();

        //执行查询语句并接受查询结果
        try {
            resultSet = SQLUtils.executeQueryPreparedStatement(sqlStr, new Object[]{subsidyProjectName});

            //循环遍历结果集并向列表中添加新的实体类
            while(resultSet.next())
            {
                subsidyList.add(new Subsidy(
                        resultSet.getInt("subsidy_id"),
                        resultSet.getString("subsidy_project_name"),
                        resultSet.getInt("subsidy_subject_id"),
                        resultSet.getInt("countryside_department_id"),
                        resultSet.getInt("city_department_id"),
                        resultSet.getInt("flag")
                ));
            }

            return subsidyListToArr(subsidyList);

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBSCUtils.close(SQLUtils.getConnection(),SQLUtils.getStatement(),resultSet);
        }

    }

    /**
     * @param subsidySubjectId 项目科目id
     * @return 项目科目名
     */
    @Override
    public String getSubsidySubjectNameById(int subsidySubjectId) {
        String sqlStr = "select subsidy_subject_name from subsidy_subject where subsidy_subject_id = ?";
        String subsidySubjectName = null;

        try {
            resultSet = SQLUtils.executeQueryPreparedStatement(sqlStr, new Object[]{subsidySubjectId});

            if (resultSet.next())
                subsidySubjectName = resultSet.getString(1);

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBSCUtils.close(SQLUtils.getConnection(),SQLUtils.getStatement(),resultSet);
        }

        return subsidySubjectName;
    }

    /**
     * @param subsidySubjectName 项目科目名
     * @return 项目科目id
     */
    @Override
    public Integer getSubsidySubjectIdByName(String subsidySubjectName) {
        String sqlStr = "select subsidy_subject_id from subsidy_subject where subsidy_subject_name = ?";
        Integer subsidySubjectId = null;

        try {
            resultSet = SQLUtils.executeQueryPreparedStatement(sqlStr, new Object[]{subsidySubjectName});

            if (resultSet.next())
                subsidySubjectId = resultSet.getInt(1);

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBSCUtils.close(SQLUtils.getConnection(),SQLUtils.getStatement(),resultSet);
        }

        return subsidySubjectId;
    }

    /**
     * @param countrysideDepartmentId
     * @return 乡镇归口单位名
     */
    @Override
    public String getCountrysideDepartmentNameById(int countrysideDepartmentId) {
        String sqlStr = "select countryside_department_name from countryside_department where countryside_department_id = ?";
        String countrysideDepartmentName = null;

        try {
            resultSet = SQLUtils.executeQueryPreparedStatement(sqlStr, new Object[]{countrysideDepartmentId});

            if (resultSet.next())
                countrysideDepartmentName = resultSet.getString(1);

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBSCUtils.close(SQLUtils.getConnection(),SQLUtils.getStatement(),resultSet);
        }

        return countrysideDepartmentName;
    }

    /**
     * @param countrysideDepartmentName 乡镇归口单位名
     * @return 乡镇归口单位id
     */
    @Override
    public Integer getCountrysideDepartmentIdByName(String countrysideDepartmentName) {
        String sqlStr = "select countryside_department_id from countryside_department where countryside_department_name = ?";
        Integer countrysideDepartmentId = null;

        try {
            resultSet = SQLUtils.executeQueryPreparedStatement(sqlStr, new Object[]{countrysideDepartmentName});

            if (resultSet.next())
                countrysideDepartmentId = resultSet.getInt(1);

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBSCUtils.close(SQLUtils.getConnection(),SQLUtils.getStatement(),resultSet);
        }

        return countrysideDepartmentId;
    }

    /**
     * @param cityDepartmentId
     * @return 县级归口单位名
     */
    @Override
    public String getCityDepartmentNameById(int cityDepartmentId) {
        String sqlStr = "select city_department_name from city_department where city_department_id = ?";
        String cityDepartmentName = null;

        try {
            resultSet = SQLUtils.executeQueryPreparedStatement(sqlStr, new Object[]{cityDepartmentId});

            if (resultSet.next())
                cityDepartmentName = resultSet.getString(1);

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBSCUtils.close(SQLUtils.getConnection(),SQLUtils.getStatement(),resultSet);
        }

        return cityDepartmentName;
    }

    /**
     * @param cityDepartmentName 县级归口单位名
     * @return 县级归口单位id
     */
    @Override
    public Integer getCityDepartmentIdByName(String cityDepartmentName) {
        String sqlStr = "select city_department_id from city_department where city_department_name = ?";
        Integer cityDepartmentId = null;

        try {
            resultSet = SQLUtils.executeQueryPreparedStatement(sqlStr, new Object[]{cityDepartmentName});

            if (resultSet.next())
                cityDepartmentId = resultSet.getInt(1);

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBSCUtils.close(SQLUtils.getConnection(),SQLUtils.getStatement(),resultSet);
        }

        return cityDepartmentId;
    }

    /**
     * 一个用于向数据库中添加subsidy的方法
     *
     * @param subsidy 需要添加的subsidy
     * @return 是否添加成功
     */
    @Override
    public boolean addSubsidy(Subsidy subsidy) {

        boolean result;
        String sqlStr = "insert into subsidy " +
                "(subsidy_project_name, subsidy_subject_id, " +
                "countryside_department_id, city_department_id, flag)" +
                " value(?,?,?,?,?)";

        System.out.println(subsidy.toString());

        result =  SQLUtils.executeUpdatePreparedStatement(sqlStr, new Object[][]{{
                  subsidy.getSubsidyProjectName(),
                  subsidy.getSubsidySubjectId(),
                  subsidy.getCountrysideDepartmentId(),
                  subsidy.getCityDepartmentId(),
                  subsidy.getFlag()
        }});

        DBSCUtils.close(SQLUtils.getConnection(),SQLUtils.getStatement(),resultSet);

        return result;

    }

    /**
     * @param id 需要启用的项目id
     * @return 是否启用成功
     */
    @Override
    public boolean startSubsidyById(int id) {
        boolean result;
        String sqlStr = "update subsidy set flag=1 where subsidy_id=?";

        result =  SQLUtils.executeUpdatePreparedStatement(sqlStr, new Object[][]{{id}});
        DBSCUtils.close(SQLUtils.getConnection(),SQLUtils.getStatement(),resultSet);
        return result;
    }

    /**
     * @param id 需要停用的项目id
     * @return 是否停用成功
     */
    @Override
    public boolean stopSubsidyById(int id) {
        boolean result;
        String sqlStr = "update subsidy set flag=0 where subsidy_id=?";

        result =  SQLUtils.executeUpdatePreparedStatement(sqlStr, new Object[][]{{id}});
        DBSCUtils.close(SQLUtils.getConnection(),SQLUtils.getStatement(),resultSet);
        return result;
    }

    /**
     * @param id 需要删除的项目id
     * @return 是否删除成功
     */
    @Override
    public boolean delSubsidyById(int id) {
        boolean result;
        String sqlStr = "delete from subsidy where subsidy_id=?";

        result =  SQLUtils.executeUpdatePreparedStatement(sqlStr, new Object[][]{{id}});
        DBSCUtils.close(SQLUtils.getConnection(),SQLUtils.getStatement(),resultSet);
        return result;
    }


    //帮助类

    /**
     * 一个用于将项目集合List为项目数组的帮助类
     *
     * @param subsidyList 需要转换的项目集合
     * @return 一个项目数组
     */
    private Subsidy[] subsidyListToArr(List<Subsidy> subsidyList) {

        //获取列表大小并初始化数组
        int size = subsidyList.size();
        Subsidy[] subsidyArr = new Subsidy[size];

        //将列表内容添加进数组
        for (int i = 0; i < size; i ++)
        {
            subsidyArr[i] = subsidyList.get(i);
        }

        //返回数组
        return subsidyArr;
    }

    @Override
    public boolean reviseSubsidy(Subsidy subsidy) {

        boolean result;
        String sqlStr = "update subsidy " +
                "set subsidy_project_name=?, subsidy_subject_id=?, " +
                "countryside_department_id=?, city_department_id=?, flag=? " +
                "where subsidy_id=?";

        result =  SQLUtils.executeUpdatePreparedStatement(sqlStr, new Object[][]{{
                subsidy.getSubsidyProjectName(),
                subsidy.getSubsidySubjectId(),
                subsidy.getCountrysideDepartmentId(),
                subsidy.getCityDepartmentId(),
                subsidy.getFlag()?1:0,
                subsidy.getSubsidyId()
        }});

        DBSCUtils.close(SQLUtils.getConnection(),SQLUtils.getStatement(),resultSet);

        return result;

    }

}
